﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for OrderTourManagement
/// </summary>
public class OrderTourManagement
{
    #region const declaration
    public const string TABLE_NAME = "OrderTour";
    public const string ORDERID = "orderID";
    public const string TOURID = "tourID";
    public const string CUSID = "cusID";
    public const string NUMADULT = "numAdult";
    public const string NUMCHILD = "numChild";
    public const string REGDATE = "regDate";
    public const string STATUS = "status";
    #endregion


	public OrderTourManagement()
	{
	}

    public static bool AddOrderTour(int tourID, int cusID, int numAdult, int numChild)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();
      
        string sqlQuery = "INSERT INTO OrderTour Values(@tourID,@cusID,@numAdult,@numChild,@regDate,0);SELECT scope_identity()";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@tourID", tourID);
        command.Parameters.AddWithValue("@cusID", cusID);
        command.Parameters.AddWithValue("@numAdult", numAdult);
        command.Parameters.AddWithValue("@numChild", numChild);
        command.Parameters.AddWithValue("@regDate", DateTime.Now);

        int n = int.Parse(command.ExecuteScalar().ToString());
        cnn.Close();

        if (n > 0)
            return true;
        else
            return false;
    }

    public static bool EditOrderTour(int orderID, int status)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "UPDATE OrderTour Set status = @status";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@status", status);

        int n = command.ExecuteNonQuery();

        cnn.Close();

        if (n == 1)
            return true;
        else
            return false;
    }

    public static bool DeleteOrderTour(int orderID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE OrderTour WHERE orderID=@orderID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@orderID", orderID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }
    public static bool DeleteOrderTourFromTour(int tourID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE OrderTour WHERE tourID=@tourID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@tourID", tourID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }
    public static bool DeleteOrderTourFromCus(int cusID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE OrderTour WHERE cusID=@cusID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@cusID", cusID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }

    public static DataSet GetAllOrder()
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        //string sqlQuery = "SELECT T.*, O.*, C.* FROM Tours T JOIN OrderTour O ON T.tourID = O.tourID JOIN Customers C ON O.cusID=C.cusID ORDER BY T.tourName";
        string sqlQuery = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY T.tourName) AS NO, T.*, O.orderID,O.numAdult,O.numChild,O.regDate, C.* FROM Tours T JOIN OrderTour O ON T.tourID = O.tourID JOIN Customers C ON O.cusID=C.cusID ) AS SUB_SQL";
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsAllTour = new DataSet();
        adapter.Fill(dsAllTour, "AllOrderTour");

        return dsAllTour;
    }

    public static DataSet GetOrders()
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        //string sqlQuery = "SELECT T.*, O.*, C.* FROM Tours T JOIN OrderTour O ON T.tourID = O.tourID JOIN Customers C ON O.cusID=C.cusID WHERE O.status = 0 ORDER BY T.tourName";
        string sqlQuery = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY T.tourName) AS NO, T.*, O.orderID,O.numAdult,O.numChild,O.regDate, C.* FROM Tours T JOIN OrderTour O ON T.tourID = O.tourID JOIN Customers C ON O.cusID=C.cusID WHERE O.status = 0) AS SUB_SQL";
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsAllTour = new DataSet();
        adapter.Fill(dsAllTour, "AllOrderTour");

        return dsAllTour;
    }

    public static DataTable GetOrderByID(int orderID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        string sqlQuery = "SELECT T.*, O.numAdult,O.numChild, C.* FROM Tours T JOIN OrderTour O ON T.tourID = O.tourID JOIN Customers C ON O.cusID=C.cusID WHERE O.orderID=" + orderID;
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsAllOrderTour = new DataSet();
        adapter.Fill(dsAllOrderTour, "OrderTourByID");

        return dsAllOrderTour.Tables["OrderTourByID"];
    }

    public static DataSet GetOrderByCus(int cusID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        string sqlQuery = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY T.tourName) AS NO, T.*, O.orderID,O.numAdult,O.numChild,O.regDate, C.* FROM Tours T JOIN OrderTour O ON T.tourID = O.tourID JOIN Customers C ON O.cusID=C.cusID WHERE O.status = 0 AND C.cusID="+cusID+") AS SUB_SQL";
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsTourByCus = new DataSet();
        adapter.Fill(dsTourByCus, "TourByCus");

        return dsTourByCus;
    }
}